<DataListSpec 
    xmlns="bb_appfx_datalist"
    xmlns:c="bb_appfx_commontypes"
    ID="6e304d54-6711-44e2-9614-45e3ffd4fa6f"
    Name="TDSM Data List"
    Description="The Tier Donor Service Model Datalist"
    Author="Blackbaud Professional Services"
    >
    
    <SPDataList SPName="USR_USP_DATALIST_TDSM">
		<c:CreateProcedureSQL>
			<![CDATA[
create procedure dbo.USR_USP_DATALIST_TDSM
(
  @BUSINESSUNITCODEID uniqueidentifier = null
 ,@DATEFILTER tinyint = 0
 ,@STARTDATE datetime = null
 ,@ENDDATE datetime = null
 ,@DESIGNATIONSELECTION_IDREGISTERESTID uniqueidentifier = null
)
as
set nocount on;
declare @date datetime = getdate();

--store these codes as variables for readability
declare
  @LastSevenDays tinyint = 0
 ,@ThisMonth tinyint = 1
 ,@FiscalYear tinyint = 2
 ,@AllDates tinyint = 3
 ,@SpecificDateRange tinyint = 4
 ;

if @LastSevenDays = @DATEFILTER begin
  set @STARTDATE = @date - 7;
  set @ENDDATE = null;
end;

if @ThisMonth = @DATEFILTER begin
  set @STARTDATE = dbo.UFN_DATE_THISMONTH_FIRSTDAY(@DATE, 0);
  set @ENDDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@DATE, 0);
end;

if @FiscalYear = @DATEFILTER begin
  set @STARTDATE = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@DATE, 0);
  set @ENDDATE = dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(@DATE, 0);
end;

if @AllDates = @DATEFILTER begin
  set @STARTDATE = null;
  set @ENDDATE = null;
end;

if @SpecificDateRange = @DATEFILTER begin
  set @STARTDATE = @STARTDATE;
  set @ENDDATE = @ENDDATE;
end;

DECLARE @ORGDATA TABLE
(
  POSITION NVARCHAR(MAX)
 ,POSITIONHOLDER NVARCHAR(154)
 ,BUSINESSUNITNAME NVARCHAR(100)
 ,MINIMUMTHRESHOLD MONEY
 ,POSITIONHOLDERCONSTITUENTID UNIQUEIDENTIFIER
 ,BUSINESSUNITCODEID UNIQUEIDENTIFIER
 ,ORGANIZATIONHIERARCHYID UNIQUEIDENTIFIER
 ,ORGANIZATIONPOSITIONID UNIQUEIDENTIFIER
 ,ORGANIZATIONPOSITIONHOLDERID UNIQUEIDENTIFIER
);
insert into @orgdata
exec USR_USP_TDSM_ORGANIZATIONS;

if @BUSINESSUNITCODEID is not null begin
  delete @orgdata where BUSINESSUNITCODEID <> @BUSINESSUNITCODEID;
end;

declare @stewardshipplandata table
(
  stewardshipplanid uniqueidentifier
 ,name nvarchar(100)
 ,constituentid uniqueidentifier
 ,designationid uniqueidentifier
 ,PLANTYPEDESCRIPTION nvarchar(100)
 ,SUBPLANTYPEDESCRIPTION nvarchar(100)
);
insert into @stewardshipplandata
select
  s.id as STEWARDSHIPPLANID
 ,s.NAME
 ,s.CONSTITUENTID
 ,us.DESIGNATIONID
 ,sptc.DESCRIPTION as PLANTYPEDESCRIPTION
 ,spsubtc.DESCRIPTION as SUBPLANTYPEDESCRIPTION
from
  STEWARDSHIPPLAN s
  inner join
  USR_STEWARDSHIPPLAN us
  on us.ID = s.ID
  left join
  STEWARDSHIPPLANTYPECODE sptc
  on sptc.ID = s.PLANTYPECODEID
  left join
  STEWARDSHIPPLANSUBTYPECODE spsubtc
  on spsubtc.ID = s.PLANSUBTYPECODEID;

declare @designationpledgeamounts table
(
  constituentid uniqueidentifier
 ,designationid uniqueidentifier
 ,totalpledgedamount money
);
insert into @designationpledgeamounts
select
  rev.CONSTITUENTID
 ,rspl.DESIGNATIONID
 ,SUM(rev.amount) as totalpledgedamount
from
  REVENUE rev
  inner join
  REVENUESPLIT rspl
  on rspl.REVENUEID = rev.ID
where rev.TRANSACTIONTYPECODE = 1 --pledge
group by
  rev.CONSTITUENTID
 ,rspl.DESIGNATIONID;

declare @designationrecordtypeid uniqueidentifier = (select id from recordtype where name = 'Designation');

--pay attention to the default revenue filter, which is stored as an xml field in REVENUEFILTER
declare @revcodes xml = (select revenuetypecodes from revenuefilter rf where isdefault = 1);
declare @revenuetypecodes table(typecode int);
insert into @revenuetypecodes
SELECT T.c.value('(ID)[1]','int') AS 'ID'
FROM @revcodes.nodes('/REVENUETYPECODES/ITEM') T( c )
where T.c.value('(SELECTED)[1]','nvarchar(50)') = 'True';

--convert filter values to exactly the right datatype to improve performance
declare @con_isinactive_filter bit = 0;
declare @rev_typecode_filter tinyint = 0;
declare @rev_startdate_filter datetimeoffset(3) = @startdate;
declare @rev_enddate_filter datetimeoffset(3) = @enddate;

--datatypes matter.  Every column should match the source exactly to assist performance.
declare @finalresults table
(
  constituentname nvarchar(154)
 ,AMOUNT money
 ,revenuedate datetimeoffset(3)
 ,designationname nvarchar(512)
 ,twelvemonths_givingtodesignation money
 ,lifetime_givingtodesignation money
 ,twelvemonths_givingtoall money
 ,totalpledgedamount money
 ,stewardshipplanname nvarchar(100)
 ,stewardshipplantype nvarchar(100)
 ,stewardshipplansubtype nvarchar(100)
 ,prospectmanagername nvarchar(154)
 ,BUSINESSUNITNAME nvarchar(100)
 ,split_typecode tinyint
 ,revenueid uniqueidentifier
 ,designationid uniqueidentifier
 ,prospectid uniqueidentifier
 ,stewardshipplanid uniqueidentifier
 ,designationrecordtypeid uniqueidentifier
 ,minimumthreshold money
 ,constituentid uniqueidentifier
);
insert into @finalresults
select
  con.name
 ,split.BASEAMOUNT
 ,rev.date
 ,des.vanityname
 ,null
 ,null
 ,null
 ,isnull(despledges.totalpledgedamount,0) as totalpledgedamount                                                        --Pledge Commitment Amount
 ,stew.NAME as stewardshipplanname                                                                                     --Stewardship plan name
 ,stew.PLANTYPEDESCRIPTION as stewardshipplantype                                                                      --Stewardship plan type
 ,stew.SUBPLANTYPEDESCRIPTION as stewardshipplansubtype                                                                --Stewardship plan subtype
 ,prosmgr.NAME as prospectmanagername                                                                                  --Prospect Manager
 ,org.BUSINESSUNITNAME
 ,split.TYPECODE
 --begin: fields used to support the "go to" buttons above the datalist
 ,rev.ID as revenueid
 ,des.ID as designationid
 ,pros.ID as prospectid
 ,stew.STEWARDSHIPPLANID as stewardshipplanid
 ,@designationrecordtypeid as DESIGNATIONRECORDTYPEID
 ,isnull(org.MINIMUMTHRESHOLD,0) as MINIMUMTHRESHOLD
 ,con.ID as constituentid
 --end: fields used to support the "go to" buttons above the datalist
from
  FINANCIALTRANSACTION rev
  inner join
  FINANCIALTRANSACTIONLINEITEM split
  on split.FINANCIALTRANSACTIONID = rev.ID
  inner join
  REVENUESPLIT_EXT splitx
  on splitx.ID = split.ID
  inner join
  CONSTITUENT con
  on con.ID = rev.CONSTITUENTID
  inner join
  DESIGNATION des
  on des.ID = splitx.DESIGNATIONID
  left join
  @designationpledgeamounts despledges
  on despledges.CONSTITUENTID = con.ID and despledges.DESIGNATIONID = des.ID
  left join
  @stewardshipplandata stew--designation is not required for the stewardship plan
  on stew.CONSTITUENTID = con.id and stew.DESIGNATIONID = des.ID
  inner join
  PROSPECT pros
  on pros.ID = con.id
  left join
  CONSTITUENT prosmgr
  on prosmgr.ID = pros.PROSPECTMANAGERFUNDRAISERID
  inner join
  @ORGDATA org
  on org.POSITIONHOLDERCONSTITUENTID = prosmgr.ID
where 1=1
  and con.ISINACTIVE = @con_isinactive_filter
  and rev.TYPECODE = @rev_typecode_filter --'payment'
  and ((@rev_startdate_filter is null) or (rev.DATE > @rev_startdate_filter))
  and ((@rev_enddate_filter is null) or (rev.DATE <= @rev_enddate_filter));

/*
Apply some filters via deletes on the temp table for performance reasons.
Delete things as early as possible so that we call the pokey
USR_UFN_REVENUE_FOR_CONSTITUENTDESIGNATION_OVER_LASTNDAYS as few times as possible.
1 - We delete anything that isn't in our default revenue filter
2 - We delete any records that don't pass the minimum threshold for the organization
--*/

delete @finalresults where split_typecode not in(select typecode from @revenuetypecodes);
update @finalresults set twelvemonths_givingtodesignation = dbo.USR_UFN_REVENUE_FOR_CONSTITUENTDESIGNATION_OVER_LASTNDAYS(constituentid,designationid,365);
delete @finalresults where twelvemonths_givingtodesignation <= minimumthreshold;
update @finalresults set lifetime_givingtodesignation = dbo.USR_UFN_REVENUE_FOR_CONSTITUENTDESIGNATION_OVER_LASTNDAYS(constituentid,designationid,365);
update @finalresults set twelvemonths_givingtoall = dbo.USR_UFN_REVENUE_FOR_CONSTITUENTDESIGNATION_OVER_LASTNDAYS(constituentid,null,365);

--if they have a designation filter, apply it here
if @DESIGNATIONSELECTION_IDREGISTERESTID is not null begin
  declare @designationfilter table(designationid uniqueidentifier);
  declare @recordtypeid uniqueidentifier = null;
  declare @ufn_name nvarchar(max) = null;
  declare @tablename nvarchar(max) = null;

  select
    @recordtypeid = RECORDTYPEID
   ,@ufn_name = dbobjectname
   ,@tablename = rt.BASETABLENAME
  from
    IDSETREGISTER ir
    inner join
    RECORDTYPE rt
    on rt.ID = ir.RECORDTYPEID
  where ir.id = @DESIGNATIONSELECTION_IDREGISTERESTID;

  declare @sqlquery nvarchar(max) = 'select t.id from dbo.' + @ufn_name + '() f inner join ' + @tablename + ' t on f.id = t.id'
  insert into @designationfilter
  exec(@sqlquery);
  delete @finalresults where designationid not in(select designationid from @designationfilter);
end;

declare @latestrevenue table
(
  prospectid uniqueidentifier
 ,designationid uniqueidentifier
 ,revenueid uniqueidentifier
);
insert into @latestrevenue
select prospectid, designationid, MAX(revenueid) as revenueid
from
  (
  select prospectid, designationid, convert(nvarchar(max),revenueid) as revenueid, MAX(revenuedate) as revenuedate
  from @finalresults
  group by prospectid, designationid, convert(nvarchar(max),revenueid)
  ) mx
group by prospectid, designationid;

select
  fr.constituentname
 ,fr.AMOUNT
 ,fr.revenuedate
 ,fr.designationname
 ,fr.twelvemonths_givingtodesignation
 ,fr.lifetime_givingtodesignation
 ,fr.twelvemonths_givingtoall
 ,fr.totalpledgedamount
 ,fr.stewardshipplanname
 ,fr.stewardshipplantype
 ,fr.stewardshipplansubtype
 ,fr.prospectmanagername
 ,fr.BUSINESSUNITNAME
 ,fr.designationid
 ,fr.prospectid
 ,fr.stewardshipplanid
 ,fr.designationrecordtypeid
from
  @finalresults fr
  inner join
  @latestrevenue latest
  on latest.prospectid = fr.prospectid and fr.designationid = latest.designationid and fr.revenueid = latest.revenueid
order by
  fr.revenuedate desc;
]]>
        </c:CreateProcedureSQL>
	</SPDataList>

    <Parameters>
		<c:FormMetaData>
			<c:FormFields>
                <c:FormField FieldID="BUSINESSUNITCODEID" Caption="Business Unit" DataType="Guid">
                    <c:CodeTable CodeTableName="BUSINESSUNITCODE" />
                </c:FormField>

                <c:FormField FieldID="DESIGNATIONSELECTION_IDREGISTERESTID" DataType="Guid" Caption="Selected designations" CaptionResourceKey="$$selected_designations">
                    <c:SearchList SearchListID="98d0070e-c4a7-495b-a438-2ac12da79068" EnableQuickFind="true">
                        <c:FormFieldOverrides>
                            <c:FormFieldOverride FieldID="RECORDTYPEID" Caption="Record type" ReadOnly="true" DefaultValueText="2B28F31E-ECBF-4AB6-88AE-78B6CE4B9259" />
                        </c:FormFieldOverrides>
                    </c:SearchList>
                </c:FormField>

                <c:FormField FieldID="DATEFILTER" DataType="Integer" Caption="Time period" DefaultValueText="0">
                    <c:ValueList>
                        <c:Items>
                            <c:Item>
                                <c:Value>0</c:Value>
                                <c:Label>7 days</c:Label>
                            </c:Item>
                            <c:Item>
                                <c:Value>1</c:Value>
                                <c:Label>This month</c:Label>
                            </c:Item>
                            <c:Item>
                                <c:Value>2</c:Value>
                                <c:Label>This fiscal year</c:Label>
                            </c:Item>
                            <c:Item>
                                <c:Value>3</c:Value>
                                <c:Label>All dates</c:Label>
                            </c:Item>
                            <c:Item>
                                <c:Value>4</c:Value>
                                <c:Label>Date range</c:Label>
                            </c:Item>
                        </c:Items>
                    </c:ValueList>
                </c:FormField>
                <c:FormField FieldID="STARTDATE" DataType="Date" Caption="Start date" CaptionResourceKey="$$start_date" />
                <c:FormField FieldID="ENDDATE" DataType="Date" Caption="End date" CaptionResourceKey="$$end_date" />
            </c:FormFields>

            <c:WebUIComponent>
                <c:UIModel AssemblyName="Blackbaud.CustomFx.ServiceBusAdapter.UIModel.dll" ClassName="Blackbaud.CustomFx.ServiceBusAdapter.UIModel.TDSMDataListUIModel" />
                <c:WebUI>
                    <c:ExternalResource Url="browser/htmlforms/custom/blackbaud.customfx.servicebusadapter/TDSMDataList.html" />
                </c:WebUI>
            </c:WebUIComponent>
        </c:FormMetaData>
	</Parameters>
    
	<Output>
		<OutputFields>
            <OutputField FieldID="CONSTITUENTNAME" Caption="Constituent name" DataType="String" />
            <OutputField FieldID="AMOUNT" Caption="Payment amount" DataType="Money" />
            <OutputField FieldID="REVENUEDATE" Caption="Revenue date" DataType="Date" />
            <OutputField FieldID="DESIGNATIONNAME" Caption="Designation" DataType="String" />
            <OutputField FieldID="TWELVEMONTHS_GIVINGTODESIGNATION" Caption="12 mos giving (Des)" DataType="Money" />
            <OutputField FieldID="LIFETIME_GIVINGTODESIGNATION" Caption="Lifetime giving (Des)" DataType="Money" />
            <OutputField FieldID="TWELVEMONTHS_GIVINGTOALL" Caption="12 mos giving (All)" DataType="Money" />
            <OutputField FieldID="TOTALPLEDGEDAMOUNT" Caption="Pledged" DataType="Money" />
            <OutputField FieldID="STEWARDSHIPPLANNAME" Caption="Stewardship plan" DataType="String" />
            <OutputField FieldID="STEWARDSHIPPLANTYPE" Caption="Plan type" DataType="String" />
            <OutputField FieldID="STEWARDSHIPPLANSUBTYPE" Caption="Plan subtype" DataType="String" />
            <OutputField FieldID="PROSPECTMANAGERNAME" Caption="Prospect manager" DataType="String" />
            <OutputField FieldID="BUSINESSUNITNAME" Caption="Business unit" DataType="String" />
            <OutputField FieldID="DESIGNATIONID" Caption="Designation ID" IsHidden="true" DataType="Guid" />
            <OutputField FieldID="PROSPECTID" Caption="Prospect ID" IsHidden="true" DataType="Guid" />
            <OutputField FieldID="STEWARDSHIPPLANID" Caption="Stewardship Plan ID" IsHidden="true" DataType="Guid" />
            <OutputField FieldID="DESIGNATIONRECORDTYPEID" Caption="Designation Record Type" IsHidden="true" DataType="Guid" />
        </OutputFields>
    </Output>
</DataListSpec>